const mysql = require('mysql');
const UUID = require('uuid');

var pool;
var table_key = {};
var table_value = {};
module.exports.table_key = table_key;
module.exports.table_value = table_value;

/**
 * 连接数据库
 * */
function connect() {
    try {
        NCUTCAT.infoLog("DB", "开始连接数据库".yellow);
        pool = mysql.createPool(NCUTCAT.localProperty.db_conn);
        pool.on('error', console.log);
        module.exports.pool = pool;
        NCUTCAT.infoLog("DB", "成功连接数据库".green);
    } catch (err) {
        NCUTCAT.error("DB - 无法连接数据库 - 5秒后重连", err);
        setTimeout(connect, 5 * 1000);
    }
}

setTimeout(() => !pool && NCUTCAT.warning("DB - 缓慢连接: 已经过3秒未成功连接数据库."), 3 * 1000);
connect();


async function init_global() {

    let connData = Object.assign({}, NCUTCAT.localProperty.db_conn);
    connData.multipleStatements = true;
    let connection = mysql.createConnection(connData);

    connection.connect();

    let fs = require('fs');
    let runSQL = async function(path) {
        let initList = fs.readdirSync(path);
        for (let key in initList) {
            let name = initList[key];
            let sql = fs.readFileSync(path + name, "utf8");
            await new Promise(r => {
                connection.query(sql, function(err, result) {
                    if (err) NCUTCAT.error("DB - init_global :" + name + " 时出错", err);
                    else NCUTCAT.infoLog("DB", "初始化完毕: " + name);
                    r();
                });
            })
        }
    }
    await runSQL("./model/db/init/"); //初始化
    await runSQL("./model/db/test-data/"); //测试数据
    connection.end();
}

let checkData_nonused = true;
/**
    检查数据是否合法
    @param data 数据
    @return true: 不合法, false: 合法
*/
function checkData(data, cb) {
    if (checkData_nonused) {
        checkData_nonused = false;
        NCUTCAT.warning(new Error("已经弃用的方法 checkData(), 应换为更安全的方法 mysql.format()"));
    }
    if (data && (typeof data === 'string') && data.indexOf('`') == -1) return false;
    err = new Error("数据不合法: " + data);
    NCUTCAT.warning("DB-DATA_CHECK", err);
    cb && cb(err);
    return true;
}

/**
    检查键是否合法
    @param key 数据
    @param table 目标数据库
    @return true: 不合法, false: 合法
*/
function checkKey(key, table, cb) {
    if (key && (typeof key === 'string') && key.indexOf('`') == -1 && table_key[key] && table_key[key].t == table) return false;
    err = new Error("键值不合法: " + key + ", 目标表: " + table + ", 原属表: " + (table_key[key] ? (table_key[key].t) : "不存在的键"));
    NCUTCAT.warning("DB-KEY_CHECK", err);
    cb && cb(err);
    return true;
}

/**
    同步方法: 执行操作
    @param sql SQL语句
    @param [datas] 数据
    @param cb 执行函数callback(err,result)
*/
function query(sql, cb) {
    try {
        pool.getConnection(function(err, connection) {
            try {
                NCUTCAT.debug("DB", `SQL语句: ${sql}`);
                if (err) cb && cb(err, "");
                else connection.query(sql, cb);
                pool.releaseConnection(connection);
            } catch (ERR) {
                NCUTCAT.error("DB - query", ERR);
            }
        });
    } catch (err) {
        if (err) { // 如果是连接断开，自动重新连接
            if (err.code === 'PROTOCOL_CONNECTION_LOST') {
                NCUTCAT.warning("DB - 连接断开, 开始重连");
                connect();
            } else {
                NCUTCAT.error("DB - query", err);
            }
        }
    }
}

/**
    同步方法: 执行操作
    @param sql SQL语句
    @param cb 执行函数callback(err,result), 返回值为下一条sql语句
*/
async function queryIterator(sql, cb) {
    query(sql, (err, res) => {
        if (err) cb(err);
        else if (sql = cb(err, res)) queryIterator(sql, cb);
    });
}

/**
    获取结构性静态数据
    table_key: 所有表命名映射
*/
async function getStaticData() {
    return new Promise(r => {
        let count = 2;
        const R = () => --count > 0 || r();
        query("SELECT * FROM `table_key`", (err, res) => { //数据表命名
            if (err) NCUTCAT.error("DB - 无法获取静态数据(table_key)", err);
            else {
                table_key = {};
                module.exports.table_key = table_key;
                for (let j in res) {
                    j = res[j];
                    table_key[j.key] = { t: j.table, v: j.value, n: j.name, c: j.class };
                }
                NCUTCAT.infoLog("DB", "静态数据: table_key: " + ("" + res.length).red + " 条数据已加载");
                R();
            }
        });

        query("SELECT * FROM `table_value`", (err, res) => {
            if (err) NCUTCAT.error("DB - 无法获取静态数据(table_value)", err);
            else {
                module.exports.table_value = table_value = {};
                for (i = 0, len = res.length; i < len; i++) {
                    let data = table_value;
                    data = data[res[i].table] || (data[res[i].table] = {});
                    data = data[res[i].key] || (data[res[i].key] = {});
                    data[res[i].value] = res[i].data;
                }
                NCUTCAT.infoLog("DB", "静态数据: table_value: " + ("" + res.length).red + " 条数据已加载");
                R();
            }
        });
    })
}

/**
    获取全部猫咪的基本数据
    @param cb callback(error, result)
*/
module.exports.getCatAll = async (cb) => {
    query("SELECT * FROM `basic_info`", (err, result) => {
        if (err) NCUTCAT.error("DB - getCatAll", err);
        cb(err, result);
    })
}

/**
    通过任意键获取猫咪的基本数据
    @param k 键类型
    @param v 待搜索的数据
    @param fuzzy 启用模糊搜索
    @param cb callback(error, result)
*/
module.exports.getCat = async (k, v, fuzzy, cb) => {
    if (checkKey(k, "basic_info", cb)) return;
    //if (checkData(v, badCb)) return;
    if (fuzzy) v = `%${v.split('').join('%')}%`;
    else v = `%${v}%`;

    let where = {};
    where[k] = v;
    query(mysql.format("SELECT * FROM `basic_info` WHERE ?;", [where]).replace('=', 'like'),
        (err, result) => {
            if (err) NCUTCAT.error("DB - getCat", err);
            cb(err, result);
        });
}

/**
    通过ID获取猫咪的基本数据
    @param id 待搜索的数据
    @param cb callback(error, result)
*/
module.exports.getCatByID = async (id, cb) => {
    if (isNaN(id)) return cb('', null);
    id = parseInt(id);
    query(mysql.format("SELECT * FROM `basic_info` WHERE ? LIMIT 1;", [{ a: id }]),
        (err, result) => {
            if (err) NCUTCAT.error("DB - getCat", err);
            cb(err, result && result[0]);
        });
}

/**
 * 获取用户信息
 * @param n 用户信息, 可以为： ID, NAME, TELEPHONE, WECHAT, EMAIL
 * @param cb 回调函数 正常情况为("",user) 出现错误为(err)
 */
module.exports.getUser = async (n, cb) => {
    //if (checkData(n, cb)) return;
    query(mysql.format(
        "SELECT * FROM user_basic WHERE nick=? OR name=? OR telephone=? OR wechat=? OR email=? LIMIT 1",
        [n, n, n, n, n]
    ), (err, res) => {
        if (err) {
            NCUTCAT.error("DB - getUser", err);
            cb(err);
        } else cb("", res.length > 0 ? res[0] : null);
    });
}

const sal = NCUTCAT.localProperty.search_amount_limit;
const searchUser_field = "nick uid name telephone wechat email".split(' ');
/**
 * 搜索用户
 * @param n 搜索关键词
 * @param cb (err,res)
 * */
module.exports.searchUser = async (n, cb) => {
    return new Promise(r => {
        if (!n) return cb && cb("", []), r([]);
        n = mysql.format('?', `%${n.toString().split("").join('%')}%`);
        query(`SELECT uid,name,nick FROM user_basic WHERE ${searchUser_field.join(` LIKE${n}OR `)} LIKE${n} LIMIT ${sal}`, (err, res) => {
            if (err) NCUTCAT.error("DB - searchUser", err), cb && cb(err), r([]);
            else res.forEach(e => e.type = 'user'), cb && cb(res), r(res);
        })
    });
}

const pwdChecker = (() => {
    try {
        return require('./pwdChecker');
    } catch (e) {
        const crypto = require("crypto");
        const fs = require("fs");
        return (email, pwd) => {
            var encoder = crypto.createHash('sha256'); //md5, sha1, sha256
            return encoder.update(pwd).digest('base64');
        }
    }
})();
/**
 * 创建用户
 * @param name 用户名
 * @param pwd 密码
 * @param email 用户邮箱
 * @param inviteCode 邀请码
 * @param cb 回调函数 正常情况为("",id) 出现错误为(err)
 * */
module.exports.createUser = async (name, pwd, email, inviteCode, cb) => {
    if (!name || !pwd || !email) return cb("bad Data");
    let data = [`usr_${name}`, name, email, pwdChecker(email, pwd), inviteCode || null];
    let sql = 'INSERT INTO user_basic(nick,name,email,pwd,inviteCode) VALUES (?)';

    query(mysql.format(sql, [data]), async (err, res) => {
        if (err) {
            NCUTCAT.error("DB - createUser", err);
            cb(err);
        } else {
            res = (res || {}).insertId;
            let res2 = await new Promise(r => query(mysql.format('INSERT INTO user_extra(uid) VALUES(?)', [res]), r));
            cb(res2, res);
        }
    });
}

/**
    获取全部用户的信息
    @param n 用户信息, 可以为： NICK, NAME, TELEPHONE, WECHAT
    @param page 页面
    @param showLimit 展示的信息
            uid    1
            NICK   2
            name   4
            tele   8
            wechat 16
            pufss  32
            p_a    64
    @param cb 回调函数 正常情况为("",user) 出现错误为(err)
*/
const suprl = NCUTCAT.localProperty.showUser_page_record_limit;
const getAllUser_showLimit = ['uid', 'nick', 'name', 'telephone', 'wechat', 'p_u_f_s_s', 'p_a'];
module.exports.getAllUser = async (n, page, showLimit, cb) => {

    n = `%${n.split('').join('%')}%`;
    page = (typeof(page) === 'number') ? page : parseInt(page);

    let s = [];
    getAllUser_showLimit.forEach((name, code) => (showLimit & (1 << code)) && s.push(name));

    let sql = "SELECT ? FROM `user_basic` WHERE `NICK` LIKE ? OR `name` LIKE ? OR `telephone` LIKE ? OR `wechat` LIKE ? LIMIT ?, ?;";
    sql = mysql.format(sql, [s, n, n, n, n, suprl * page, suprl]);

    query(sql, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - getAllUser", err);
            cb(err);
        } else cb("", res);
    });
}

/**
    获取用户信息
    @param n 用户NAME
    @param cb 回调函数 正常情况为("",user) 出现错误为(err)
*/
module.exports.getUserByName = async (n, cb) => {
    console.log(n, new Error());
    query(mysql.format("SELECT * FROM `user_basic` WHERE `name` = ? LIMIT 1", [n]),
        (err, res) => {
            if (err) {
                NCUTCAT.error("DB - getUserByName", err);
                cb(err);
            } else cb("", res.length > 0 ? res[0] : null);
        }
    );
}

/**
 * 获取用户信息
 * @param uid 用户UID
 * @param cb 回调函数 正常情况为("",user) 出现错误为(err)
 */
module.exports.getUserByID = async (n, cb) => {
    return new Promise(r => {
        query(mysql.format("SELECT * FROM user_basic WHERE uid=? LIMIT 1", [n]), (err, res) => {
            if (err) {
                NCUTCAT.error("DB - getUserByID", err);
                cb && cb(err), r(null);
            } else {
                res = res.length > 0 ? res[0] : null;
                cb && cb("", res), r(res);
            }
        });
    });
}

/**
 * 获取用户名称和昵称
 * @param uid 用户UID
 * @param cb 回调函数 正常情况为("",user) 出现错误为(err)
 * */
module.exports.getUserName = async (uid, cb) => {
    return new Promise(r => {
        uid = parseInt(uid);
        if (isNaN(uid)) return cb("NaN");
        query(mysql.format('SELECT nick,name FROM user_basic WHERE uid=? LIMIT 1', [uid]), (err, res) => {
            if (err) {
                NCUTCAT.error("DB - getUserName", err);
                cb && cb(err);
                r(null);
            } else {
                res = res.length > 0 ? res[0] : null;
                cb && cb('', res);
                r(res);
            }
        });
    });
}

/**
 * 获取用户关注/粉丝数据
 * @param id 用户ID
 * @param page 页码
 * @param cb 回调函数 正常为("",data), 出现错误为(err)
 * */
module.exports.getUserFollows = async (id, page, cb) => {
    page = parseInt(page);
    if (isNaN(page)) return cb("NaN");
    let follow = mysql.format('SELECT fid,time,isPrivate,isSpecial,`group` FROM user_follow WHERE uid=? LIMIT ?,?', [id, suprl * page, suprl]);
    query(follow, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - getUserFollows", err);
            cb(err);
        } else cb('', res);
    });
}

/**
 * 获取用户关注/粉丝数据
 * @param id 用户ID
 * @param page 页码
 * @param cb 回调函数 正常为("",data), 出现错误为(err)
 * */
module.exports.getUserFans = async (id, page, cb) => {
    page = parseInt(page);
    if (isNaN(page)) return cb("NaN");
    let fan = mysql.format('SELECT uid AS tid,time,(SELECT id FROM user_follow WHERE fid=tid AND uid=? ) as also FROM user_follow WHERE fid=? AND isPrivate=0 LIMIT ?,?', [id, id, suprl * page, suprl]);
    query(fan, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - getUserFans", err);
            cb(err);
        } else cb('', res);
    });
}

/**
 * 获取用户关注/粉丝数据
 * @param id 用户ID
 * @param page 页码
 * @param cb 回调函数 正常为("",data), 出现错误为(err)
 * */
module.exports.getUserFollowsCount = async (id, cb) => {
    let follow = mysql.format('SELECT count(1)as r FROM user_follow WHERE uid=?;', [id]);
    query(follow, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - getUserFollowsCount", err);
            cb(err);
        } else cb('', res[0].r);
    });
}

/**
 * 获取用户关注/粉丝数据
 * @param id 用户ID
 * @param page 页码
 * @param cb 回调函数 正常为("",data), 出现错误为(err)
 * */
module.exports.getUserFansCount = async (id, cb) => {
    let fan = mysql.format('SELECT count(1)as r FROM user_follow WHERE fid=? AND isPrivate=0;', [id]);
    query(fan, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - getUserFansCount", err);
            cb(err);
        } else cb('', res[0].r);
    });
}

/**
 * 检查是否有用户关注
 * @param uid 用户ID
 * @param fid 被关注ID
 * @param cb 回调函数 正常为("",isFollow), 出现错误为(err)
 * */
module.exports.hasUserFollow = async (uid, fid, cb) => { //TODO 分页显示
    return new Promise(r => {
        if (isNaN(fid) || isNaN(uid)) return cb && cb("NaN"), r(false);
        uid = parseInt(uid);
        fid = parseInt(fid);
        query(mysql.format('SELECT id FROM user_follow WHERE uid=? AND fid=? LIMIT 1', [uid, fid]), (err, res) => {
            if (err) NCUTCAT.error("DB - hasUserFollow", err);
            res = !err && !!(res && res.length);
            cb && cb(err, res), r(res);
        });
    });
}

/**
 * 设置用户关注
 * @param uid 用户ID
 * @param fid 被关注ID
 * @param isPrivate 悄悄关注
 * @param isSpecial 特别关注
 * @param group 分组
 * @param cb 回调函数 正常为("",data), 出现错误为(err)
 * */
module.exports.setUserFollow = async (uid, fid, isPrivate, isSpecial, group, cb) => { //TODO 分页显示
    if (isNaN(fid) || isNaN(uid)) return cb("NaN");
    uid = parseInt(uid);
    fid = parseInt(fid);
    let unfollow = fid < 0;
    if (unfollow) fid = -fid;
    if (uid === fid) return cb("不能关注自己");
    if (unfollow) {
        return query(mysql.format('DELETE FROM user_follow WHERE uid=? AND fid=?', [uid, fid]), (err, res) => {
            if (err) {
                NCUTCAT.error("DB - setUserFollow - DELETE", err);
                cb(err);
            } else cb('', !!(res || {}).affectedRows);
        });
    }
    isPrivate = isPrivate ? 1 : 0;
    isSpecial = isSpecial ? 1 : 0;
    group = group || null;
    query(mysql.format('SELECT id FROM user_follow WHERE uid=? AND fid=? LIMIT 1', [uid, fid]), (ERR, RES) => {
        if (RES.length) {
            query(mysql.format('UPDATE user_follow SET ? WHERE id=?', [{
                isPrivate: isPrivate,
                isSpecial: isSpecial,
                group: group
            }, RES[0].id]), (err, res) => {
                if (err) {
                    NCUTCAT.error("DB - setUserFollow - UPDATE", err);
                    cb(err);
                } else cb('', !!(res || {}).affectedRows);
            });
        } else {
            let sql = mysql.format('INSERT INTO `user_follow`(uid,fid,isPrivate,isSpecial,`group`) VALUES (?,?,?,?,?)',
                [uid, fid, isPrivate, isSpecial, group]);
            query(sql, (err, res) => {
                if (err) {
                    if (err.errno == 1452) {
                        cb("找不到用户");
                    } else {
                        NCUTCAT.error("DB - setUserFollow - INSERT", err);
                        cb(err);
                    }
                } else cb('', !!(res || {}).affectedRows);
            });
        }
    });
}

/**
 * 设置用户头像
 * @param uid 用户UID
 * @param file 文件
 * @param cb 回调函数 正常为("",uid), 出现错误为(err)
 * 
 * */
module.exports.setUserSketch = async (uid, file, cb) => {
    uid = parseInt(uid);
    if (isNaN(uid)) return cb("NaN");
    let sql = mysql.format('UPDATE user_extra SET sketch=!!FILE!! WHERE uid=?', [uid]);
    file = NCUTCAT.tools.utf8BufferToHex(file);
    query(sql.replace(/!!FILE!!/, file), (err, res) => {
        if (err) {
            NCUTCAT.error("DB - setUserSketch", err);
            cb(err);
        } else cb(err, uid);
    });
}

/**
 * 设置用户头像
 * @param uid 用户UID
 * @param icon 图标
 * @param cb 回调函数 正常为("",uid), 出现错误为(err)
 * 
 * */
module.exports.setUserSketchIcon = async (uid, icon, cb) => {
    uid = parseInt(uid);
    if (isNaN(uid)) return cb("NaN");
    let sql = mysql.format('UPDATE user_extra SET sketchIcon=? WHERE uid=?', [icon, uid]);
    query(sql, (err, res) => {
        if (err) NCUTCAT.error("DB - setUserSketchIcon", err)
        cb(err, err ? undefined : uid);
    });
}

/**
 * 获取用户头像
 * @param uid 用户UID
 * 
 * */
module.exports.getUserSketch = async (uid, cb) => {
    return new Promise(r => {
        uid = parseInt(uid);
        if (isNaN(uid)) {
            cb && cb("NaN");
            r(null);
            return;
        }
        let sql = mysql.format("SELECT sketch,sketchIcon FROM user_extra WHERE uid=? LIMIT 1;", [uid]);
        query(sql, (err, res) => {
            if (err) {
                NCUTCAT.error("DB - getUserSketch", err);
                cb && cb(err);
                r(null);
            } else {
                res = res.length > 0 ? res[0] : null;
                cb && cb(err, res);
                r(res);
            }
        });
    });
}

/**
 * 上传图片
 * @param file 文件(Buffer)
 * @param fileType 文件类型(HTTP通用)
 * @param userID 上传者ID
 * @param threadID 帖子ID
 * @param lore 图片描述
 * @param cb 回调函数(err,UUID)
 */
module.exports.addThreadImg = async (file, fileType, userID, threadID, lore, cb) => {
    if (isNaN(userID)) return cb("NaN");
    let count = await new Promise(resolve => query(mysql.format('SELECT count(1)as r FROM thread_img WHERE uid=? AND tid IS NULL', [userID]), (e, r) => resolve(e || r && r[0].r)));
    if (typeof count !== 'number') return NCUTCAT.error("DB - addUserCacheImg", count), cb(count);
    if (count >= 50) return cb("缓存过多");

    let SQL, uuid = '';
    const insert = () => query((SQL = mysql.format(`INSERT INTO thread_img(uid,pid,data,type,lore) VALUES (?,${uuid},!!FILE!!,?,?)`, [userID, fileType, lore])).replace(/!!FILE!!/, NCUTCAT.tools.utf8BufferToHex(file)), (err, res) => {
        if (err) return err.sql = SQL, NCUTCAT.error("DB - addThreadImg", err), cb(err);
        if (threadID && !isNaN(threadID)) {
            module.exports.setThreadImgTid(uuid, userID, parseInt(tid), e0 => {
                if (e0) return cb(e0);
                cb("", uuid);
            }, true);
        } else cb("", uuid);
    });
    let sql = () => `SELECT 1 FROM thread_img WHERE pid=${uuid = NCUTCAT.tools.UUIDtoHex(UUID.v4())} LIMIT 1;`;
    queryIterator(sql(), (err, res) => {
        if (err) NCUTCAT.error("DB - addThreadImg", err), cb(err);
        else if (res.length > 0) return sql();
        else insert();
    });
}

/**
 * 设置帖子图片所属的帖子ID
 * @param uuid 图片ID
 * @param uid 用户ID
 * @param tid 帖子ID
 * @param cb 回调函数(err)
 * */
module.exports.setThreadImgTid = async (uuid, uid, tid, cb, force) => {
    if (isNaN(uid) || isNaN(tid)) return cb('NaN');
    uuid = NCUTCAT.tools.UUIDtoHex(uuid);
    if (uuid === '0x0') return cb("bad UUID");
    const UPDATE = () => query(mysql.format(`UPDATE thread_img SET tid=? WHERE pid=${uuid}`, [tid]), cb);
    if (force) UPDATE();
    else query(mysql.format(`SELECT tid FROM thread_img WHERE pid=${uuid} AND uid=? LIMIT 1;`, [uid]), (err, res) => {
        if (err) NCUTCAT.error("DB - setThreadImgTid", err), cb && cb(err);
        else if (!(res.length > 0)) cb && cb('找不到的图片');
        else if (res[0].tid) cb && cb('已经设置所属帖子');
        else UPDATE();
    });
}

/**
    获取猫咪图片
    @param uuid 图片UUID
    @param infoCb 回调函数 正常情况为("",imginfo) 出现错误为(err)
    @param dataCb 回调函数 正常情况为("",imgdata,imginfo) 出现错误为(err)
*/
module.exports.getThreadImg = async (uuid, infoCb, dataCb) => {
    uuid = NCUTCAT.tools.UUIDtoHex(uuid);
    if (uuid === '0x0') return infoCb("bad UUID");
    query("SELECT pid,tid,uid,status FROM thread_img WHERE pid=" + uuid + " LIMIT 1;",
        (err, res) => {
            if (err) {
                NCUTCAT.error("DB - getThreadImg - info", err);
                infoCb(err);
            } else if (infoCb("", res = (res.length > 0 ? res[0] : null)))
                query("SELECT data,type,lore FROM thread_img WHERE pid=" + uuid + " LIMIT 1;",
                    (err2, res2) => {
                        if (err2) {
                            NCUTCAT.error("DB - getCatImg - data", err2);
                            dataCb(err2);
                        } else dataCb("", res2.length > 0 ? res2[0] : null, res);
                    }
                );
        }
    );
}

/**
 * 上传图片
 * @param file 文件(Buffer)
 * @param fileType 文件类型(HTTP通用)
 * @param userID 上传者ID
 * @param catID 对应猫咪ID
 * @param description 图片描述
 * @param cb 回调函数(err,UUID)
 */
module.exports.addCatImg = async (file, fileType, userID, catID, description, cb) => {
    if (isNaN(userID)) return cb("NaN");
    let uuid = '';
    let sql = () => 'SELECT 1 FROM `cat_img` WHERE cat_img.q = ' + (uuid = NCUTCAT.tools.UUIDtoHex(UUID.v4())) + ' LIMIT 1;';
    queryIterator(sql(), (ERR, RES) => { //先查询UUID 防止UUID碰撞
        if (ERR) {
            NCUTCAT.error("DB - addImg", ERR);
            cb(ERR);
        } else if (RES.length > 0) return sql();
        else {
            let sql = mysql.format("INSERT INTO `cat_img`(`q`, `r`, `s`, `t`, `u`, `v`) VALUES (" + `${uuid}, ?,?,!!FILE!!,?,?);`,
                [parseInt(userID), parseInt(catID), description, fileType]);
            file = NCUTCAT.tools.utf8BufferToHex(file);
            query(sql.replace(/!!FILE!!/, file),
                (err, res) => {
                    if (err) {
                        if (err.code === 'ER_NO_REFERENCED_ROW_2') return cb('找不到猫咪');
                        err.sql = sql;
                        NCUTCAT.error("DB - addImg", err);
                        cb(err);
                    } else cb(err, uuid)
                });
        }
        return null;
    });
}

/**
 * 检测猫咪图片是否存在
 * @param uuid 图片UUID
 * @return 是否存在
 * */
module.exports.hasCatImg = (uuid) => {
    return new Promise(r => {
        uuid = NCUTCAT.tools.UUIDtoHex(uuid);
        if (uuid === '0x0') return r(null);
        query(`SELECT 1 FROM cat_img WHERE q = ${uuid} LIMIT 1`, (err, res) => r(err ? false : !!res.length));
    })
}

/**
    获取猫咪图片
    @param uuid 图片UUID
    @param infoCb 回调函数 正常情况为("",imginfo) 出现错误为(err)
    @param dataCb 回调函数 正常情况为("",imgdata,imginfo) 出现错误为(err)
*/
module.exports.getCatImg = async (uuid, infoCb, dataCb) => {
    uuid = NCUTCAT.tools.UUIDtoHex(uuid);
    if (uuid === '0x0') return infoCb("bad UUID");
    query("SELECT `p`,`q`,`r`,`s`,`u`,`v`,`ax` FROM `cat_img` WHERE `q`=" + uuid + " LIMIT 1",
        (err, res) => {
            if (err) {
                NCUTCAT.error("DB - getCatImg - info", err);
                infoCb(err);
            } else if (infoCb("", res = (res.length > 0 ? res[0] : null)))
                query("SELECT `t` FROM `cat_img` WHERE `q` =" + uuid + " LIMIT 1",
                    (err2, res2) => {
                        if (err2) {
                            NCUTCAT.error("DB - getCatImg - data", err2);
                            dataCb(err2);
                        } else dataCb("", res2.length > 0 ? res2[0] : null, res);
                    }
                );
        }
    );
}

const ciprl = NCUTCAT.localProperty.catImg_page_record_limit;
/**
    获取一只猫咪的所有图片
    @param cid 猫咪ID
    @param page 页码
    @param cb 回调函数
*/
module.exports.getCatImgs = async (cid, page, cb) => {
    page = (typeof(page) === 'number') ? page : parseInt(page);
    query(mysql.format(`SELECT \`q\`,\`u\`,\`ax\` FROM \`cat_img\` WHERE ? LIMIT ${ciprl*page}, ${ciprl};`, [{ s: cid }]), (err, res) => {
        if (err) NCUTCAT.error("DB - getCatImgsRandom", err);
        cb(err, res);
    });
}

module.exports.getCatImgsRandom = async (cid, uid, limit, examine, cb) => {
    if (cid && isNaN(cid) || uid && isNaN(uid) || isNaN(limit)) return cb('NaN');
    cid = parseInt(cid);
    uid = parseInt(uid);

    let where = cid ? 's=' + cid : '';
    where && uid && (where += ' OR ');
    uid && (where += 'r=' + uid);
    where && (where = `(${where})`);

    examine = examine instanceof Array ? `ax=${examine.join(' OR ax=')}` : '';
    examine && (examine = `(${examine})`);

    let sql = `SELECT q,u FROM cat_img${where||examine?' WHERE ':''}${examine}${where&&examine?' AND ':''}${where} ORDER BY RAND() LIMIT ${limit};`;
    query(sql, (err, res) => {
        if (err) NCUTCAT.error("DB - getCatImgsRandom", err);
        cb(err, res);
    });
}
const createCat_DataCheck = x => (x || typeof x === 'number' && !isNaN(x)) ? x : null;
var createCat_NumberData = ['e', 'g', 'h', 'i', 'k', 'm'];
var createCat_AllData = [`aa`, `b`, `c`, `d`, `e`, `f`, `g`, `h`, `i`, `j`, `k`, `l`, `m`, `n`, `o`, 'ay'];
var createCat_sql = `INSERT INTO \`basic_info\`(\`${createCat_AllData.join('`,`')}\`) VALUES (?);`;//由table key加载
/**
    创建猫咪
    @param d 猫咪数据
    @param cb 回调函数(err,res)
*/
module.exports.createCat = async (d, cb) => {

    createCat_NumberData.forEach(t => d[t] !== undefined && (d[t] = parseInt(d[t]))); //转换数据类型
    let pid = d.aa ? NCUTCAT.tools.UUIDtoHex(d.aa) : null;
    d.aa = d.aa ? '!!!HEX-1!!!' : null;
    d.bm=0;

    let data = [];
    createCat_AllData.forEach(t => data.push(createCat_DataCheck(d[t]))); //生成数据数组

    let sql = mysql.format(createCat_sql, [data]); //生成sql语句
    d.aa && (sql = sql.replace(`'${d.aa}'`, pid));

    query(sql, (err, res) => {
        if (err) NCUTCAT.error("DB - createCat", err);
        cb(err, (res || {}).insertId);
    }); //执行sql任务
}

var updateCat_NumberData = createCat_NumberData;
var updateCat_AllData = createCat_AllData;
const updateCat_sql = "UPDATE `basic_info` SET ? WHERE ?";
/**
    更新猫咪
    @param d 猫咪数据
    @param cb 回调函数(err,res)
*/
module.exports.updateCat = async (d, cb) => { //TODO 这里有BUG (编辑时部分数据未上传)
    console.log(d, JSON.stringify(d));
    updateCat_NumberData.forEach(t => d[t] !== undefined && (d[t] = parseInt(d[t]))); //转换数据类型
    let pid = d.aa ? NCUTCAT.tools.UUIDtoHex(d.aa) : null;
    d.aa = d.aa ? '!!!HEX-1!!!' : null;

    let data = {};
    updateCat_AllData.forEach(t => d[t] !== undefined && (data[t] = createCat_DataCheck(d[t]))); //生成数据数组

    console.log(data, JSON.stringify(data));
    let sql = mysql.format(updateCat_sql, [data, { a: d.a }]); //生成sql语句
    if (d.aa) sql = sql.replace(`'${d.aa}'`, pid);

    query(sql, (err, res) => {
        if (err) NCUTCAT.error("DB - updateCat", err);
        cb(err, (res || {}).insertId);
    }); //执行sql任务
}

/**
    获取猫咪关系
    @param id 猫咪ID
    @param cb callback(error, result)
*/
module.exports.getCatRelation = async (id, cb) => {
    if (isNaN(id)) return cb('', null);
    id = parseInt(id);
    query(mysql.format("SELECT * FROM cat_relation WHERE ? OR ?", [{ w: id }, { x: id }]),
        (err, result) => {
            if (err) NCUTCAT.error("DB - getCatRelation", err);
            cb(err, result);
        });
};

/**
    删除猫咪
    @param d 猫咪数据
    @param cb 回调函数(err,res)
*/
module.exports.removeCat = async (id, cb) => {
    query(mysql.format('DELETE FROM `basic_info` WHERE ?', [{ a: id }]),
        (err, res) => {
            console.log(res);
            cb(err, (res || {}).message);
        });
}

const nprl = NCUTCAT.localProperty.notice_page_record_limit;
/**
获取通知信息
@param getAll 是否获取所有通知信息
@param page 信息页码
@param cb 回调函数(err,res)
*/
module.exports.getNotice = async (getAll, page, cb) => {
    page = (typeof(page) === 'number') ? page : parseInt(page);
    query(`SELECT * FROM notice ${getAll?'':"WHERE ae <= now() AND (ah & 2) = 0 "} LIMIT ${nprl*page}, ${nprl};`, cb);
}

/**
 * 上传帖子
 * @param tid 目标帖子ID, 若为创建新帖子, 请将其置为false等值
 * @param uid 用户ID
 * @param title 文章标题
 * @param data 文章主体
 * @param cb 回调函数(err,res)
 * */
module.exports.uploadThread = async (tid, uid, title, data, cb) => {
    return new Promise(r => {
        let sql = tid ?
            mysql.format('UPDATE thread SET aj=?,am=?,an=? WHERE ai=?', [uid, title, data, tid]) :
            mysql.format('INSERT INTO thread(aj,am,an) VALUES (?,?,?)', [uid, title, data]);
        query(sql, (err, res) => {
            if (err) NCUTCAT.error("DB - uploadThread", err);
            tid = tid || (res && res.insertId);
            cb && cb(err, tid);
            r(err ? null : tid);
        });
    });
}

/**
 * 获取帖子信息
 * @param tid 目标帖子ID
 * @param cb 回调函数(err,res)
 * @return {aj,ak,am,al,ao,ap}
 * */
module.exports.getThreadInfo = async (tid, cb) => {
    return new Promise(r => {
        let sql = mysql.format("SELECT aj,ak,am,al,ao,ap FROM thread WHERE ai=? LIMIT 1", [tid]);
        query(sql, (err, res) => {
            if (err) NCUTCAT.error("DB - getThreadInfo", err);
            res = res && res.length > 0 ? res[0] : null;
            cb && cb(err, res);
            r(res);
        });
    });
}

/**
 * 获取帖子
 * @param tid 目标帖子ID
 * @param cb 回调函数(err,res)
 * @return {*}
 * */
module.exports.getThread = async (tid, cb) => {
    return new Promise(r => {
        if (isNaN(tid) || ((tid = parseInt(tid)) <= 0)) return cb && cb('NaN'), r(null);
        let sql = mysql.format("SELECT t.*,count(l.user)as`like`FROM thread AS t LEFT JOIN thread_like AS l ON thread=ai WHERE ai=? LIMIT 1;", [tid]);
        query(sql, (err, res) => {
            if (err) NCUTCAT.error("DB - getThread", err);
            res = res && res.length > 0 ? res[0] : null;
            cb && cb(err, res);
            r(res);
        });
    });
}

/**
 * 检测用户是否给帖子点赞
 * @param uid 用户ID
 * @param tid 帖子ID
 * @param cb 回调函数(err,has)
 * @return has
 * */
module.exports.hasThreadLike = async (uid, tid, cb) => {
    return new Promise(r => {
        if (isNaN(tid) || isNaN(uid)) return cb && cb('NaN'), r(false);
        tid = parseInt(tid), uid = parseInt(uid);
        let sql = mysql.format('SELECT 1 FROM thread_like WHERE thread=? AND`user`=?', [tid, uid]);
        query(sql, (err, res) => {
            if (err) NCUTCAT.error("DB - hasThreadLike", err), cb && cb(err), r(false);
            else res = res && res.length > 0, cb && cb("", res), r(res);
        });
    })
}

/**
 * 设置帖子点赞
 * @param uid 用户ID 使用用户ID的负数来取消点赞
 * @param tid 帖子ID
 * @param cb 回调函数(err,res)
 * @return 是否修改
 * */
module.exports.setThreadLike = async (uid, tid, cb) => {
    return new Promise(r => {
        if (isNaN(tid) || isNaN(uid)) return cb && cb('NaN'), r(false);
        if (uid > 0) {
            let sql = mysql.format('INSERT INTO thread_like(thread,`user`)VALUES (?,?)', [tid, uid]);
            query(sql, (err, res) => {
                if (err) {
                    if (err.code === 'ER_DUP_ENTRY') return cb && cb("", false), r(false);
                    NCUTCAT.error("DB - setThreadLike - INSERT", err)
                    cb && cb(err), r(false);
                } else cb && cb("", !!res), r(!!res);
            });
        } else {
            let sql = mysql.format('DELETE FROM thread_like WHERE thread=? AND`user`=?', [tid, -uid]);
            query(sql, (err, res) => {
                if (err) {
                    NCUTCAT.error("DB - setThreadLike - DELETE", err)
                    cb && cb(err), r(false);
                } else res = res && !!res.affectedRows, cb && cb("", res), r(res);
            });
        }
    });
}

const searchThread_field = "ai am".split(' ');
/**
 * 搜索帖子
 * @param n 搜索关键词
 * @param cb (err,res)
 * */
module.exports.searchThread = async (n, cb) => {
    return new Promise(r => {
        if (!n) return cb && cb("", []), r([]);
        n = mysql.format('?', `%${n.toString().split("").join('%')}%`);
        query(`SELECT ai,aj,am,al FROM thread WHERE ${searchThread_field.join(` LIKE${n}OR `)} LIKE${n} LIMIT ${sal}`, (err, res) => {
            if (err) NCUTCAT.error("DB - searchThread", err), cb && cb(err), r([]);
            else res.forEach(e => e.type = 'thread'), cb && cb(res), r(res);
        })
    });
}

const tprl = NCUTCAT.localProperty.thread_page_record_limit;
/**
 * 列出用户帖子列表
 * @param uid 用户ID
 * @param limit 允许的浏览限制状态ID字符串, 用逗号分割, 请自行确保数据安全
 * @param page 列表页码
 * @param cb(err,res)
 * @return [threadInfo1,threadInfo2,...]
 * @return threadInfo {ai: 帖子编号, l: 点赞数, pid: 第一张图片, am: 帖子标题}
 * */
module.exports.listUserThread = async (uid, limit, page, cb) => {
    if (isNaN(uid) || isNaN(page)) return cb("NaN");
    page = parseInt(page);
    let sql = `SELECT ai,am,count(\`user\`)AS \`like\`,pid FROM thread LEFT JOIN thread_like ON ai=thread LEFT JOIN thread_img ON ai=tid WHERE aj=${uid} AND FIND_IN_SET(ap,'${limit}')GROUP BY ai LIMIT ${tprl*page}, ${tprl}`;
    // console.log(sql.yellow);
    query(sql, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - listUserThread", err);
            return cb(err);
        }
        res && res.forEach(e => e.pid && (e.pid = NCUTCAT.tools.BufferToHex(e.pid)));
        cb("", res);
    })
}

/**
 * 列出用户帖子列表
 * @param type 类型编号
 * @param limit 允许的浏览限制状态ID字符串, 用逗号分割, 请自行确保数据安全
 * @param page 列表页码
 * @param cb(err,res)
 * @return [threadInfo1,threadInfo2,...]
 * @return threadInfo {ai: 帖子编号, l: 点赞数, pid: 第一张图片, am: 帖子标题}
 * */
module.exports.listNewsThread = async (type, limit, page, cb) => {
    if (isNaN(type) || isNaN(page)) return cb("NaN");
    page = parseInt(page);
    let tagCheck = type ? `AND(SELECT count(tag)FROM thread_tag INNER JOIN tags ON tags.az=thread_tag.tag AND tags.bc=${type} WHERE thread=ai)` : '';
    let sql = `SELECT ai,aj,am,count(\`user\`)AS\`like\`,pid FROM thread LEFT JOIN thread_like ON ai=thread LEFT JOIN thread_img ON ai=tid WHERE FIND_IN_SET(ap,'${limit}')${tagCheck}GROUP BY ai LIMIT ${tprl*page},${tprl}`;
    query(sql, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - listNewsThread", err);
            return cb(err);
        }
        res && res.forEach(e => e.pid && (e.pid = NCUTCAT.tools.BufferToHex(e.pid)));
        cb("", res);
    });
}

/**
 * 统计用户发过帖子的数量(包括所有帖子, 无论是否被删除或审核不通过)
 * @param uid 用户ID
 * @param cb 回调函数(err,count)
 * */
module.exports.userThreadCount = async (uid, cb) => {
    return new Promise(r => {
        if (isNaN(uid)) return cb && cb("NaN"), r(-1);
        let sql = mysql.format('SELECT count(1)as r FROM thread WHERE aj=?;', [uid]);
        query(sql, (err, res) => {
            if (err) {
                NCUTCAT.error("DB - listUserThread", err);
                return cb && cb(err), r(-1);
            } else return (res = res[0].r), cb && cb("", res), r(res);
        })
    })
}

/**
 * 创建tag
 * @param name tag名称
 * @param cb 回调函数(err,res)
 * */
module.exports.createTag = async (name, cb) => {
    if (typeof name === 'string') name = name.trim().toLowerCase();
    else return cb("错误数据");
    if (name.length > 30) return cb("字符串过长");
    else if (!isNaN(name)) return cb("不可纯数字");
    let sql = mysql.format('INSERT INTO tags(ba) VALUES (?)', [name]);
    query(sql, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - createTag", err);
            cb(err);
        } else cb("", res && res.insertId);
    });
}

/**
 * 搜索标签<br>
 * 复合判断: 当特殊类型为3时, 检查猫咪信息
 * @param name  标签名/id
 * @param cb 回调函数(err,res)
 * @return res {id:name,...}
 * */
module.exports.searchTag = async (name, cb) => {
    return new Promise(r => {
        name = name.trim().toLowerCase();
        if (name.length > 30) return cb && cb("", {}), r([]);
        name = `%${name.split("").join('%')}%`;
        let sql = 'SELECT t.* FROM tags AS t,basic_info AS b WHERE((t.bc<>3 OR t.bc IS NULL)AND(az LIKE ? OR ba LIKE ?))OR(t.bc=3 AND t.bd=b.a AND(b.b LIKE ? OR b.c LIKE ?))GROUP BY t.az'.replace(/\?/g, mysql.format('?', [name]));
        query(sql, (err, res) => {
            if (err) {
                NCUTCAT.error("DB - searchTag", err);
                cb(err);
            } else {
                if (cb) {
                    let result = {};
                    res.forEach(e => result[e.az] = e.ba);
                    cb("", result);
                }
                res.forEach(e => e.type = 'tag');
                r(res);
            }
        });
    });
}

/**
 * 获取标签
 * @param name 标签名/id 纯数字将会被当作ID
 * @param cb 回调函数(err,res)
 * @Deprecated 简易查询, 没有extra数据查询
 * */
module.exports.getTag = async (name, cb) => {
    !isNaN(name) && (name = parseInt(name));
    let sql = mysql.format(`SELECT * FROM tags WHERE ${isNaN(name)?'ba':'az'}=? LIMIT 1`, name);
    query(sql, (err, res) => {
        if (err) {
            NCUTCAT.error("DB - getTag", err);
            cb(err);
        } else cb("", res && res.length > 0 && res[0]);
    })
}

/**
 * 获取标签集合
 * @param arr 标签ID集合
 * @param cb 回调函数(err,res)
 * */
module.exports.getTags = async (arr, cb) => {
    return new Promise(r => {
        if (typeof arr === 'string') arr = arr.split(',');
        let data = [];
        arr.forEach(e => isNaN(e) || data.push(e));
        let sql = `SELECT * FROM tags WHERE FIND_IN_SET(az,'${data.join()}');`;
        query(sql, (err, res) => {
            if (err) {
                NCUTCAT.error("DB - getTags", err);
                cb && cb(err);
            } else cb && cb("", res);
            r(res);
        });
    });
}

/**
 * 设置帖子的标签(全覆盖)
 * 会先删除已经存在的标签, 再按照给定的标签设置
 * @param tid 帖子ID
 * @param tagNumbers 标签ID集合
 * @param cb 回调函数(err,len)
 * */
module.exports.setThreadTag = async (tid, tagNumbers, cb) => {
    if (isNaN(tid)) return cb && cb("NaN - tid");
    let data = [];
    tagNumbers.forEach(e => isNaN(e) || data.push(`(${tid},${e})`));
    let len = data.length;
    data = data.join();
    await query(mysql.format('DELETE FROM thread_tag WHERE thread=?;', [tid]));
    let sql = 'INSERT INTO thread_tag(thread,tag)VALUES' + data;
    query(sql, (err, res) => {
        if (err) {
            if (err.code === 'ER_NO_REFERENCED_ROW_2') return cb && cb('找不到帖子或标签');
            else {
                NCUTCAT.error("DB - setThreadTag", err);
                cb && cb(err);
            }
        } else cb && cb("", len);
    });
}

/**
 * 获取帖子标签
 * @param tid 帖子ID
 * @param cb 回调函数(err,res)
 * */
module.exports.getThreadTag = async (tid, cb) => {
    return new Promise(r => {
        if (isNaN(tid) || ((tid = parseInt(tid)) <= 0)) return cb && cb('NaN'), r(null);
        let sql = mysql.format("SELECT tag FROM thread_tag WHERE thread=?", [tid]);
        query(sql, (err, res) => {
            if (err) {
                NCUTCAT.error("DB - getThreadTag", err);
                cb && cb(err);
                return r(null);
            }
            let data = new Array(res.length);
            res.forEach((e, i) => data[i] = e.tag);
            cb && cb("", data);
            r(data);
        });
    })
}

/**
 * 保存猫的热度数据
 * @param data {id:val}
 * @return Package
 * */
module.exports.saveCatHot = async (data) => {
    let arrWhen = [];
    let arrWhere = [];
    for (let id in data) {
        if (isNaN(id)) NCUTCAT.error('DB - saveCatHot', 'NaN id: ' + id);
        if (isNaN(data[id])) NCUTCAT.error('DB - saveCatHot', 'NaN data: ' + id);
        arrWhen.push(`WHEN ${id} THEN ${data[id]}`);
        arrWhere.push(parseInt(id));
    }
    let sql = `UPDATE basic_info SET bm=CASE a ${arrWhen.join(" ")} END WHERE a IN(?);`;
    return new Promise(r => {
        query(mysql.format(sql, [arrWhere]), (err, res) => {
            if (err) NCUTCAT.error("DB - saveCatHot", err);
            r(res);
        });
    })
}
/**
 * 获取猫的热度数据
 * @return [{id,val}]
 * */
module.exports.getCatHot = async () => {
    let sql = "SELECT a AS id,bm AS val FROM basic_info;"
    return new Promise(r => {
        query(sql, (err, res) => {
            if (err) NCUTCAT.error("DB - getCatHot", err);
            r(res || []);
        });
    });
}

(async () => {
    // await init_global();//初始化全局数据库(覆盖)
    await getStaticData(); //启动时自动获取

    //设置basic_info字段
    createCat_NumberData = [];
    createCat_AllData = [];
    const numberClass = new Set('enum', 'age');
    for (let k in table_key)
        if (table_key[k].t === 'basic_info' && table_key[k].c) createCat_AllData.push(k), (table_key[k].c === 'age' || table_key[k].c === 'enum') && createCat_NumberData.push(k);
    createCat_sql = `INSERT INTO \`basic_info\`(\`${createCat_AllData.join('`,`')}\`) VALUES (?);`;
    updateCat_NumberData = createCat_NumberData;
    updateCat_AllData = createCat_AllData;

    NCUTCAT.infoLog("DataManager", "成功加载");
})();